<?php
/**
* エラーチェッククラス
**/
require_once( '../system/Class_M_DAO.php' );
class Class_M_AJAREKS extends Class_M_DAO {
	//protected properties begin
	/**
	 *
	 * テーブル名
	 *
	 * @access    protected
	 * @var       string
	 *
	 */
	protected $_table = "M_AJAREKS";
	
	/**
	 *
	 * 主キー名
	 *
	 * @access    protected
	 * @var       string
	 *
	 */
	protected $_pkey  = "id";
	
	/**
	 *
	 * ソート順
	 *
	 * @access    protected
	 * @var       string
	 *
	 */
	 protected $_order = "userid";
	 
	 protected $_cols     = array(
		"aja_cd	"     => array(AOKI_VARTYPE_STR),
		"useid"       => array(AOKI_VARTYPE_STR),
		"user_name"   => array(AOKI_VARTYPE_STR),
		"shop_cd"     => array(AOKI_VARTYPE_STR)
	 );

	 protected $_checks  = array(
		"userid"      => array( "EXIST" ),	
		"user_name"   => array( "EXIST" ),	
		"shop_cd"     => array( "EXIST" )
	 );
 
	 //maxlength 
	 protected $_maxlens  = array( );
	//search($where ,&$page = NULL ,$myselect = NULL,$mycountsql = NULL, $orderby = NULL){	
	 public function search($where ,&$page = NULL,$shop_name = NULL ){
	 	$innerjoin = "";
	 	if (!empty($shop_name)) {
	 		//$whereshopname = "WHERE bb.shop_name like '%{$shop_name}%'";
	 		$innerjoin = " inner join  (select DISTINCT sa.userid from   M_AJAREKS  sa  INNER JOIN M_SHOP sp ON sa.shop_cd = sp.shop_cd AND  sp.shop_name like '%{$shop_name}%' ) spn on spn.userid = a.id ";
	 	}
	 	$ajarekstable = "select aa.userid,max(aa.u_time) as u_time,max(aa.u_user) as u_user,count(*) as shop_count   from  M_AJAREKS  aa  INNER JOIN M_SHOP bb ON aa.shop_cd = bb.shop_cd  group by aa.userid";
	 	
		if (!empty($shop_name)) {
 			$fromtable = " FROM M_LOGIN a inner join ($ajarekstable ) b on a.id=b.userid  ";
		}else {
			$fromtable = " FROM M_LOGIN a left join ($ajarekstable ) b on a.id=b.userid  ";
		}
		
		$fromtable = $fromtable. $innerjoin;
		$sql    = "SELECT a.id,a.login_id,a.user_name,a.auth, b.u_time,b.shop_count,(select  user_name FROM M_LOGIN where id  = b.u_user) as m_user_name ".$fromtable;
		
		$sqlcnt = "SELECT count(*) as cnt  ".$fromtable;
		
		$orderby = "  a.login_id";

		return  parent::search($where ,$page ,$sql, $sqlcnt, $orderby);
	}
	
	public function searchcsv($where ,&$page = NULL,$shop_name = NULL ){

		$innerjoin = "";
	 	if (!empty($shop_name)) {
	 		//$whereshopname = "WHERE bb.shop_name like '%{$shop_name}%'";
	 		$innerjoin = " inner join  (select DISTINCT sa.userid from   M_AJAREKS  sa  INNER JOIN M_SHOP sp ON sa.shop_cd = sp.shop_cd AND  sp.shop_name like '%{$shop_name}%' ) spn on spn.userid = a.id ";
	 	}
	 	
	 	$ajarekstable = "select aa.userid,max(aa.u_time) as u_time,max(aa.u_user) as u_user,count(*) as shop_count   from  M_AJAREKS  aa " .
	 			" INNER JOIN M_SHOP bb ON aa.shop_cd = bb.shop_cd  group by aa.userid" ;
	 	$fromtable = " FROM M_LOGIN a  ";
	 	
		if (!empty($shop_name)) {
 			$fromtable = $fromtable." INNER JOIN  M_AJAREKS b on a.id=b.userid INNER JOIN M_SHOP bb ON b.shop_cd = bb.shop_cd  ";
		}else {
			$fromtable = $fromtable." LEFT JOIN M_AJAREKS b on a.id=b.userid  LEFT JOIN M_SHOP bb ON b.shop_cd = bb.shop_cd  ";
		}
	
		$fromtable = $fromtable. $innerjoin;
		$sql    = " select b.*, bb.shop_name, a.login_id, a.user_name,a.auth,(select  user_name FROM M_LOGIN where id  = b.u_user) as m_user_name   ".$fromtable;
		
		$orderby = "  a.login_id";
		return  parent::search($where ,$page ,$sql, NULL, $orderby);
	}
	
	/**
	 *
	 * 単一レコードを取得する
	 *
	 * @access    public
	 * @param     mixed     $id             主キーの値
	 * @return    mixed     取得に失敗した場合にFALSE, 成功した場合にレコードの内容(array)
	 */
	public function getByUseid($value) {
	    $fromtable = " FROM  M_LOGIN  a left join (select aa.* ,bb.shop_name from {$this->_table} aa  INNER JOIN M_SHOP bb ON aa.shop_cd = bb.shop_cd where aa.userid = '{$value}' ) c on  a.id = c.userid ";
		$where  =  " WHERE a.id = '{$value}'" ; 
		//$sql = sprintf("SELECT * FROM {$this->_table}  WHERE useid = '%s'" , $value );
		$sql = "SELECT a.id,a.user_name,a.auth,c.ID as cid, c.zone_cd, c.u_time,c.shop_cd as shop_cd,c.shop_name as shop_name, (select  user_name FROM M_LOGIN where id  = c.u_user) as m_user_name" .$fromtable.$where;
		
		$this->_db->query( $sql );
		
		if( $this->_db->num_rows() ){
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[] = $row;
			}
			return $data;
		}else{
			return  FALSE;
		}
	}
	
	public function getShopCount($value) {
		$sql = "SELECT count(*)  as cnt FROM {$this->_table} WHERE userid='{$value}'";
		$this->_db->query( $sql );
		if( $this->_db->num_rows() ){
			$row = $this->_db->fetch();
			return $row['cnt'] ; 
		}else{
			return  0;
		}
	}
	
	public function getUserid($where, $auth) {
		$sql = "SELECT b.id  as id FROM {$this->_table} a " .
				" INNER JOIN M_LOGIN b on a.id=b.userid and b.auth ={$auth} ";
		$this->_db->query( $sql.$where );
		if( $this->_db->num_rows() ){
			$row = $this->_db->fetch();
			return $row['id'] ; 
		}else{
			return  FALSE;
		}
	}
	
	//$value  2:エリア   3:ゾーン 
	public function getUsersByAja($value) {
		$sql = "SELECT b.id,a.shop_cd FROM {$this->_table} a " .
		" INNER JOIN M_LOGIN b on b.id=a.userid and b.auth ={$value} ";
		$this->_db->query( $sql );
		if( $this->_db->num_rows() ){
			$data = array();
			while( $row = $this->_db->fetch() ){
				//
				$data[$row["shop_cd"]][] = $row["id"];
			}
			return $data;
		}else{
			return  FALSE;
		}
	}
	
	//
	public function delAja($value = null) {
		$sql = "delete  FROM {$this->_table}  ";
		if ($this->exec_sql($sql)) {
			return TRUE;
		}
		return  FALSE;
		
	}
}

?>